package com.pro01.backend.mapper;

import com.pro01.backend.dto.ActiveTorrentDTO;
import com.pro01.backend.dto.TorrentWithConnectionDTO;
import com.pro01.backend.pojo.TorrentClass;
import org.apache.ibatis.annotations.*;

import java.sql.Timestamp;
import java.util.List;
import java.util.Map;

@Mapper
public interface TorrentMapper {

    // 插入新的种子记录（新增 info_hash 字段）
    @Insert("INSERT INTO torrents (info_hash, user_id, name, file_path, category, size, seeders, leechers, status, uploaded_at) " +
            "VALUES (#{infoHash}, #{userId}, #{name}, #{filePath}, #{category}, #{size}, 0, 0, 'ACTIVE', NOW())")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insertTorrent(TorrentClass torrent);

    // 获取所有种子
//    @Select("SELECT * FROM torrents WHERE status = 'ACTIVE'")
//    List<TorrentClass> getAllTorrents();
    // 分页查询种子
//    @Select("SELECT * FROM torrents WHERE status = 'ACTIVE' ORDER BY uploaded_at DESC LIMIT #{limit} OFFSET #{offset}")
//    List<TorrentClass> getAllTorrentsPaged(@Param("offset") int offset, @Param("limit") int limit);
    // 查询总种子数
//    @Select("SELECT COUNT(*) FROM torrents WHERE status = 'ACTIVE'")
//    int countAllTorrents();
// 分页查询所有种子（不限制状态）
    @Select("SELECT * FROM torrents ORDER BY uploaded_at DESC LIMIT #{limit} OFFSET #{offset}")
    List<TorrentClass> getAllTorrentsPaged(@Param("offset") int offset, @Param("limit") int limit);

    // 查询所有种子数（不限制状态）
    @Select("SELECT COUNT(*) FROM torrents")
    int countAllTorrents();

    // 模糊搜索种子 + 分页
    @Select("SELECT * FROM torrents WHERE name LIKE CONCAT('%', #{keyword}, '%') AND status = 'ACTIVE' LIMIT #{offset}, #{pageSize}")
    List<TorrentClass> searchTorrentsByKeyword(@Param("keyword") String keyword,
                                               @Param("offset") int offset,
                                               @Param("pageSize") int pageSize);

    // 统计搜索结果总数
    @Select("SELECT COUNT(*) FROM torrents WHERE name LIKE CONCAT('%', #{keyword}, '%') AND status = 'ACTIVE'")
    int countTorrentsByKeyword(@Param("keyword") String keyword);

    // 用户查看自己上传的种子（分页）
    @Select("SELECT * FROM torrents WHERE user_id = #{userId} AND status = 'ACTIVE' LIMIT #{offset}, #{pageSize}")
    List<TorrentClass> getUserUploadedTorrents(@Param("userId") Long userId,
                                               @Param("offset") int offset,
                                               @Param("pageSize") int pageSize);

    // 根据 ID 获取种子
    @Select("SELECT * FROM torrents WHERE id = #{id}")
    TorrentClass getTorrentById(Long id);

    // 查询用户上传种子数
    @Select("SELECT COUNT(*) FROM torrents WHERE user_id = #{userId} AND status = 'ACTIVE'")
    int countUserUploadedTorrents(@Param("userId") Long userId);

    // 根据分类筛选种子
//    @Select("SELECT * FROM torrents WHERE category = #{category} AND status = 'ACTIVE'")
//    List<TorrentClass> getTorrentsByCategory(String category);
    // 分页查询某分类下的种子
    @Select("SELECT * FROM torrents WHERE category = #{category} AND status = 'ACTIVE' ORDER BY uploaded_at DESC LIMIT #{limit} OFFSET #{offset}")
    List<TorrentClass> getTorrentsByCategoryPaged(@Param("category") String category, @Param("offset") int offset, @Param("limit") int limit);
    // 查询某分类下的种子总数
    @Select("SELECT COUNT(*) FROM torrents WHERE category = #{category} AND status = 'ACTIVE'")
    int countTorrentsByCategory(@Param("category") String category);


    // 根据 info_hash 获取种子（用于 Tracker /announce）
    @Select("SELECT * FROM torrents WHERE info_hash = #{infoHash}")
    TorrentClass getTorrentByInfoHash(String infoHash);

//    @Select("SELECT u.id AS user_id, u.username, COUNT(t.id) AS upload_count " +
//            "FROM users u " +
//            "LEFT JOIN torrents t ON u.id = t.user_id " +
//            "WHERE t.status = 'ACTIVE' OR t.status IS NULL " +
//            "GROUP BY u.id, u.username")
//    List<Map<String, Object>> countAllUsersUploadsWithUsername();

    // 分页查询所有用户的上传次数
    @Select("SELECT u.id AS user_id, u.username, COUNT(t.id) AS upload_count " +
            "FROM users u " +
            "LEFT JOIN torrents t ON u.id = t.user_id " +
            "WHERE t.status = 'ACTIVE' OR t.status IS NULL " +
            "GROUP BY u.id, u.username " +
            "ORDER BY upload_count DESC " +
            "LIMIT #{limit} OFFSET #{offset}")
    List<Map<String, Object>> countAllUsersUploadsPaged(@Param("offset") int offset, @Param("limit") int limit);

    // 查询总用户数（共用之前的）
    @Select("SELECT COUNT(*) FROM users")
    int countUsers();

    /**
     * 以下是管理员的接口
     */

    // 查询所有种子（不管状态），分页
    @Select("SELECT id, info_hash, user_id, name, file_path, category, size, status, uploaded_at " +
            "FROM torrents " +
            "ORDER BY uploaded_at DESC " +
            "LIMIT #{offset}, #{pageSize}")
    List<TorrentClass> getAllTorrentsForAdmin(@Param("offset") int offset, @Param("pageSize") int pageSize);

    // 查询种子总数量
    @Select("SELECT COUNT(*) FROM torrents")
    int countAllTorrentsForAdmin();

    // 删除种子
    @Update("UPDATE torrents SET status = 'DELETED' WHERE id = #{id}")
    int deleteTorrent(Long id);

    // 🔥 搜索种子，返回种子信息 + 上传者用户名，按时间倒序
    @Select("SELECT t.*, u.username " +
            "FROM torrents t " +
            "LEFT JOIN users u ON t.user_id = u.id " +
            "WHERE t.name LIKE CONCAT('%', #{keyword}, '%') " +
            "ORDER BY t.uploaded_at DESC " +
            "LIMIT #{offset}, #{pageSize}")
    List<Map<String, Object>> searchTorrentsWithUploader(@Param("keyword") String keyword,
                                                         @Param("offset") int offset,
                                                         @Param("pageSize") int pageSize);

    // 🔥 搜索总数
    @Select("SELECT COUNT(*) " +
            "FROM torrents t " +
            "WHERE t.name LIKE CONCAT('%', #{keyword}, '%')")
    int countTorrentsByName(@Param("keyword") String keyword);


    // 🔥 根据上传者用户名模糊查询种子（分页）
    @Select("SELECT t.*, u.username " +
            "FROM torrents t " +
            "LEFT JOIN users u ON t.user_id = u.id " +
            "WHERE u.username LIKE CONCAT('%', #{uploaderName}, '%') " +
            "ORDER BY t.uploaded_at DESC " +
            "LIMIT #{offset}, #{pageSize}")
    List<Map<String, Object>> searchTorrentsByUploader(@Param("uploaderName") String uploaderName,
                                                       @Param("offset") int offset,
                                                       @Param("pageSize") int pageSize);

    // 🔥 上传者用户名模糊查询总数
    @Select("SELECT COUNT(*) " +
            "FROM torrents t " +
            "LEFT JOIN users u ON t.user_id = u.id " +
            "WHERE u.username LIKE CONCAT('%', #{uploaderName}, '%')")
    int countTorrentsByUploader(@Param("uploaderName") String uploaderName);

    // 🔥 根据种子状态筛选查询（分页）
    @Select("SELECT t.*, u.username " +
            "FROM torrents t " +
            "LEFT JOIN users u ON t.user_id = u.id " +
            "WHERE t.status = #{status} " +
            "ORDER BY t.uploaded_at DESC " +
            "LIMIT #{offset}, #{pageSize}")
    List<Map<String, Object>> searchTorrentsByStatus(@Param("status") String status,
                                                     @Param("offset") int offset,
                                                     @Param("pageSize") int pageSize);

    // 🔥 种子状态查询总数
    @Select("SELECT COUNT(*) " +
            "FROM torrents " +
            "WHERE status = #{status}")
    int countTorrentsByStatus(@Param("status") String status);

    /**
     * 接下来是种子促销逻辑
     */
    // 设置促销(仅限管理员)
    @Update("UPDATE torrents SET promotion_type = #{promotionType}, promotion_end = #{promotionEnd} WHERE id = #{id}")
    int updatePromotionStatus(@Param("id") Long torrentId,
                     @Param("promotionType") String promotionType,
                     @Param("promotionEnd") java.sql.Timestamp promotionEnd);

    // 查询当前有效促销种子
    @Select("""
        SELECT id, name, category, size, promotion_type, promotion_end
        FROM torrents
        WHERE promotion_type != 'NONE' AND promotion_end > NOW() AND status = 'ACTIVE'
        ORDER BY promotion_end ASC
        LIMIT #{offset}, #{limit}
    """)
    List<Map<String, Object>> getActivePromotions(@Param("offset") int offset, @Param("limit") int limit);

    // 查询有效促销总数
    @Select("""
        SELECT COUNT(*)
        FROM torrents
        WHERE promotion_type != 'NONE' AND promotion_end > NOW() AND status = 'ACTIVE'
    """)
    int countActivePromotions();

    // 修改促销状态
    @Update("""
        UPDATE torrents
        SET promotion_type = #{promotionType}, promotion_end = #{promotionEnd}
        WHERE id = #{torrentId}
    """)
    int updatePromotionInfo(@Param("torrentId") Long torrentId,
                            @Param("promotionType") String promotionType,
                            @Param("promotionEnd") Timestamp promotionEnd);

    // 取消促销
    @Update("""
    UPDATE torrents
    SET promotion_type = 'NONE', promotion_end = NULL
    WHERE id = #{torrentId}
""")
    int resetPromotionInfo(@Param("torrentId") Long torrentId);

    // 找到文件大小和上传者id
    @Select("SELECT size FROM torrents WHERE id = #{torrentId}")
    Long getTorrentSize(@Param("torrentId") Long torrentId);

    @Select("SELECT user_id FROM torrents WHERE id = #{torrentId}")
    Long getUploaderId(@Param("torrentId") Long torrentId);

    // 连接性检测
    @Select("""
    SELECT t.id, t.name, t.category, t.size, t.seeders, t.leechers, t.uploaded_at
    FROM torrents t
    JOIN (
        SELECT DISTINCT torrent_id
        FROM tracker_reports
        WHERE event != 'STOPPED'
          AND created_at > NOW() - INTERVAL 30 MINUTE
        ORDER BY torrent_id DESC
        LIMIT #{limit} OFFSET #{offset}
    ) r ON t.id = r.torrent_id
""")
    List<ActiveTorrentDTO> getActiveTorrentsPaged(@Param("offset") int offset, @Param("limit") int limit);


    @Select("""
    SELECT COUNT(DISTINCT torrent_id)
    FROM tracker_reports
    WHERE event != 'STOPPED'
      AND created_at > NOW() - INTERVAL 30 MINUTE
""")
    int countActiveTorrents();

    // 种子连接性检测
    @Select("""
SELECT 
    t.id, t.name, t.category, t.size, t.seeders, t.leechers, t.uploaded_at,
    CASE 
        WHEN r.torrent_id IS NOT NULL THEN TRUE 
        ELSE FALSE 
    END AS connected
FROM torrents t
LEFT JOIN (
    SELECT DISTINCT torrent_id
    FROM tracker_reports
    WHERE event != 'STOPPED' 
      AND created_at > NOW() - INTERVAL 30 MINUTE
) r ON t.id = r.torrent_id
ORDER BY t.uploaded_at DESC
LIMIT #{limit} OFFSET #{offset}
""")
    List<TorrentWithConnectionDTO> getTorrentsWithConnection(@Param("offset") int offset,
                                                             @Param("limit") int limit);

}
